package com.zut.dao.impl;

import com.zut.dao.TeacherDao;
import com.zut.entity.Teacher;
import com.zut.util.JdbcUtil;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SingleColumnRowMapper;

import java.util.List;
import java.util.Map;

/**
 * 教师持久层，操作teacher表
 */
public class TeacherDaoImpl implements TeacherDao {

    //接收JdbcTemplate
    private JdbcTemplate template = JdbcUtil.template;


    /**
     * 根据教师编号和密码查询教师
     * @param tid
     * @param password
     * @return
     */
    @Override
    public Teacher findTeacherByTidAndPassword(String tid, String password) {
        String sql = "select * from teacher where id=? and password=?";

        try {
            Teacher teacher = template.queryForObject(sql, new BeanPropertyRowMapper<>(Teacher.class), tid, password);
            return teacher;
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }

    /**
     * 根据班级id查询该班暂缺的课程教师信息
     * @param classId
     * @return
     */
    @Override
    public List<Map<String, Object>> findTeachersByClassId(String classId) {
        String sql = "select t.id,t.name,c.cname from teacher t,course c where t.cid = c.id and cid  not in (select cid from teachingclass tc,course c,teacher t where tc.tid=t.id and t.cid = c.id and tc.classid = ?)";

        return template.queryForList(sql,classId);
    }

    /**
     * 查询教师最大编号
     * @return
     */
    @Override
    public String findMaxTid() {

        String sql = "select max(id) from teacher";

        try {
            String maxId = template.queryForObject(sql, new SingleColumnRowMapper<>(String.class));
            return maxId;
        }catch (EmptyResultDataAccessException e){

            return null;
        }

    }

    /**
     * 添加
     * @param teacher
     */
    @Override
    public void addTeacher(Teacher teacher) {

        String sql = "insert into teacher values(?,?,?,?,?,?,?,null)";

        template.update(sql,teacher.getId(),teacher.getName(),teacher.getGender(),teacher.getPhoneNum(),teacher.getAddress(),teacher.getPassword(),teacher.getJoinedDate());

    }

    /**
     * 查询所有教师信息
     * @return
     */
    @Override
    public List<Map<String, Object>> findAllTeachers() {
        String sql= " select t.id,t.name,t.gender,t.phoneNum,c.cname from teacher t left join course c on t.cid = c.id";
        return template.queryForList(sql);
    }

    /**
     * 根据姓名模糊查询
     * @param s
     * @return
     */
    @Override
    public List<Map<String, Object>> findTeachersLikeName(String s) {
        String sql= " select t.id,t.name,t.gender,t.phoneNum,c.cname from teacher t left join course c on t.cid = c.id where t.name like ?";
        return template.queryForList(sql,s);
    }

    /**
     * 根据课程名称模糊查询
     * @param s
     * @return
     */
    @Override
    public List<Map<String, Object>> findTeachersByCname(String s) {
        String sql= " select t.id,t.name,t.gender,t.phoneNum,c.cname from teacher t left join course c on t.cid = c.id where c.cname like ?";
        return template.queryForList(sql,s);

    }

    /**
     * 修改教师的课程
     * @param tid
     * @param cid
     */
    @Override
    public void updateTeachCourse(String tid, String cid) {
        String sql = "update teacher set cid=? where id = ?";
        template.update(sql,cid,tid);
    }

    /**
     * 删除表中数据
     * @param tid
     * @return
     */
    @Override
    public int deleteByTid(String tid) {
        return template.update("delete from teacher where id=?",tid);
    }

    /**
     * 修改教师表密码
     * @param id
     * @param newPwd
     */
    @Override
    public void updateTeacherPassword(String id, String newPwd) {
        String sql = "update teacher set password=? where id=?";
        template.update(sql,newPwd,id);

    }

    /**
     * 修改手机号码
     * @param id
     * @param newPhoneNum
     */
    @Override
    public void updateTeacherPhoneNum(String id, String newPhoneNum) {
        String sql = "update teacher set phoneNum=? where id=?";
        template.update(sql,newPhoneNum,id);
    }
}
